# -*- coding: utf-8 -*-

import xlrd
import csv


def get_xls():
    wb = xlrd.open_workbook('vat_prd_V38.0.1.xlsx')
    sh = wb.sheet_by_name('sheet1')
    print(sh.nrows)
    print(sh.ncols)
    cols = []

    data = []
    print(sh.row_values(0))

    # for row in range(8):
    for row in range(sh.nrows):
        # print(row)
        if not row:
            cols = sh.row_values(0)
        else:
            data.append(sh.row_values(row))

    fields = {
        'code': 0, 'name': 2, 'shortname': 3,
        'ratio_str': 4, 'vat_special': 5, 'grouped': 6,
        'active': 7,
        'note': 8
    }

    records = []

    for one in data:
        code = one[fields['code']]
        shortname = one[fields['shortname']]
        name = one[fields['name']]

        res_id = 'fapiao.vat_prd_' + code
        pid = len(code) > 2 and 'fapiao.vat_prd_' + code[:-2] or ''
        ratio_str = one[fields['ratio_str']]
        vat_special = one[fields['vat_special']]
        grouped = one[fields['grouped']]
        grouped = grouped == '是' and 'TRUE' or 'FALSE'
        active = one[fields['active']]
        active = active == '是' and 'TRUE' or 'FALSE'
        note = one[fields['note']]

        rec = [res_id, code, pid, ratio_str, vat_special, grouped, active,
               shortname,  name, note]

        records.append(rec)

    return records


def set_csv(records):
    headers = [
        'id', 'code', 'parent_id/id',
        'ratio_str', 'vat_special', 'grouped', 'active',
        'shortname', 'name', 'note'
    ]

    with open('prd.csv', 'w') as f:
        fcsv = csv.writer(f)
        fcsv.writerow(headers)
        fcsv.writerows(records)


def get_xls2():
    wb = xlrd.open_workbook('ass.xlsx')
    sh = wb.sheet_by_name('Sheet1')
    # print(sh.nrows)
    # print(sh.ncols)

    cols = []

    data = []
    # print(sh.row_values(0))

    for row in range(sh.nrows):
        # print(row)
        if not row:
            cols = sh.row_values(0)
        else:
            data.append(sh.row_values(row))

    # print(cols)

    # for col, index in enumerate(cols):
    #     print(col, index)

    #   '税控盘机器编号',

    fields = [
        'order_num', 'date_order', 'type', 'invoice_type',
        'buyer_name', 'buyer_tin', 'buyer_address', 'buyer_phone',
        #  'buyer_bank', 'buyer_account',
        'saler_address', 'saler_phone',
        # 'saler_bank', 'saler_account',
        'payee_name', 'checker_name', 'drawer_name', 'note'
    ]

    line_fields = [
        'line_num', 'vat_product_code',
        'product_name', 'product_spec', 'product_uom',
        'quantity', 'price_unit',
        # 'tax_ratio',
    ]

    records = {}

    for one in data:
        record = {}
        for index, val in enumerate(one):
            record[cols[index]] = val

        order_num = record['order_num']

        if not records.get(order_num):
            records[order_num] = {}
        order = records[order_num]
        for fld in fields:
            order[fld] = record[fld]

        order['buyer_bank_account'] = record['buyer_bank'] + \
            ' ' + record['buyer_account']

        order["saler_tin"] = tin
        order['saler_bank_account'] = record['saler_bank'] + \
            ' ' + record['saler_account']

        order['taxmachine_code'] = machineCode

        if not order.get('line_ids'):
            order['line_ids'] = []

        lines = order['line_ids']
        ln = {}

        for fld in line_fields:
            ln[fld] = record[fld]

        ln['tax_ratio'] = record['tax_ratio'] / 100

        ln["price_tax_selection"] = "tax_included"

        lines.append(ln)

        # records.append(record)
        # print(one)
    # print(data)
    # print(cols)

    # for key, val in records.items():
    #     print(key, val)

    # pass

    recs = list(records.values())

    # for one in recs:
    #     print(one)

    return recs


if __name__ == '__main__':

    records = get_xls()
    set_csv(records)
